package team02.server.bean;

import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import team02.server.model.MySQLModel;

public class UserBean extends MySQLModel {

	public UserBean() {
		super(null);
	}

	public static final int LEVEL_SYSTEM_ADMIN = 1;
	public static final int LEVEL_ORG_ADMIN = 2;
	public static final int LEVEL_TEST_MAKER = 3;
	public static final int LEVEL_EXAMINER = 4;
	// `id` ,
	// `name`,
	// `username`,
	// `password`,
	// `email`,
	// `alias`,
	// `access_level`,
	// `status`
	private static final String FIELD_NAME = "`id`, `" + TABLE_USER
			+ "`.`name`, `username`, `password`, `" + TABLE_USER
			+ "`.`email`, `" + TABLE_USER + "`.`alias`, `access_level`, `"
			+ TABLE_USER + "`.`status`";

	int userId;
	String username;
	String password;
	String name;
	String email;
	int accessLevel;
	int status;
	String alias;
	String aliasRequest;
	String userType;
	int org_status;

	public int getOrg_status() {
		return org_status;
	}
	public void setOrg_status(int org_status) {
		this.org_status = org_status;
	}
	@Override
	//全てのユーザ情報を削除する。logout,login unsuccessful のときに使う
	public void clear() {
		this.userId = 0;
		this.username = null;
		this.password = null;
		this.name = null;
		this.email = null;
		this.alias = null;
		this.accessLevel = 0;
		this.status = 0;
		this.aliasRequest = null;
		this.userType = null;
		this.org_status = 0;
	}
//何に使うか、まだわからない
	public int login() {
		java.util.Date dt = new java.util.Date();
		long today = dt.getTime();
		String query = "SELECT jp_user.name FROM jp_user, jp_organization WHERE" +
				" jp_user.alias=jp_organization.alias AND jp_organization.status=0 " +
				"AND `jp_user.username` = '"+ username+ "' " +
				"AND jp_user.password='"+ password+ "' " +
				"AND jp_organization.expired_date >= '" + today + "'";
		try {
			initConnection();
			executeQuery(query);
			if (resultSet.next()) {
				this.userId = resultSet.getInt(1);
				this.name = resultSet.getString(2);
				this.username = resultSet.getString(3);
				this.password = resultSet.getString(4);
				this.email = resultSet.getString(5);
				this.alias = resultSet.getString(6);
				this.accessLevel = resultSet.getInt(7);
				if (accessLevel == 2)
					this.userType = "団体管理者";
				if (accessLevel == 3)
					this.userType = "出題者";
				if (accessLevel == 4)
					this.userType = "採点者";
				this.status = resultSet.getInt(8);
				freeResources();
				return 0;
			} else {
				freeResources();
				return 1;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 1;
	}

	@Override
	// ユーザの情報を収得する
	public int load() throws SQLException {
		String queryString = "SELECT " + FIELD_NAME + " FROM " + TABLE_USER;
		// ログインしているユーザの情報を収得
		if (userId != 0) {
			queryString += " WHERE `id` = " + userId;
			// ログインしていないユーザ
		} else if (username != null && aliasRequest != null) {
			queryString += " WHERE `username` = '" + username + "'"
					+ "AND `alias` = '" + aliasRequest + "' AND password='"
					+ password + "'";
		} else {
			return 1;// load failed
		}
		initConnection();

		executeQuery(queryString);
		if (resultSet.next()) {
			this.userId = resultSet.getInt(1);
			this.name = resultSet.getString(2);
			this.username = resultSet.getString(3);
			this.password = resultSet.getString(4);
			this.email = resultSet.getString(5);
			this.alias = resultSet.getString(6);
			this.accessLevel = resultSet.getInt(7);
			if (accessLevel == 2)
				this.userType = "団体管理者";
			if (accessLevel == 3)
				this.userType = "出題者";
			if (accessLevel == 4)
				this.userType = "採点者";
			this.status = resultSet.getInt(8);
			queryString="SELECT status FROM jp_organization WHERE alias='"+this.alias+"'";
			executeQuery(queryString);
			if(resultSet.next())
			{
				this.org_status=resultSet.getInt("status");
			}
			freeResources();
			return 0;
		} else {
			freeResources();
			return 1;
		}
	}
//団体の団体管理者を収得する
	public int loadOrgAdmin(String alias) throws SQLException {
		String queryString = "SELECT * FROM jp_user WHERE alias='" + alias
				+ "' AND access_level='2'";
		initConnection();

		executeQuery(queryString);
		if (resultSet.next()) {
			this.userId = resultSet.getInt(1);
			this.name = resultSet.getString(2);
			this.username = resultSet.getString(3);
			this.password = resultSet.getString(4);
			this.email = resultSet.getString(5);
			this.alias = resultSet.getString(6);
			this.accessLevel = resultSet.getInt(7);
			if (accessLevel == 2)
				this.userType = "団体管理者";
			if (accessLevel == 3)
				this.userType = "出題者";
			if (accessLevel == 4)
				this.userType = "採点者";
			this.status = resultSet.getInt(8);
			queryString="SELECT status FROM jp_organization WHERE alias='"+this.alias+"'";
			executeQuery(queryString);
			if(resultSet.next())
			{
				this.org_status=resultSet.getInt("status");
			}
			freeResources();
			return 0;
		} else {
			freeResources();
			return 1;
		}
	}

	@Override
	//ユーザ情報を更新する
	public int save() throws SQLException {
		String queryUpdate = "UPDATE " + TABLE_USER + " SET `name`='" + name
				+ "',`username`='" + username + "',`password`='" + password
				+ "',`email`='" + email + "', `access_level`=" + accessLevel
				+ ", `status`=" + status;

		if (userId != 0) {
			queryUpdate += " WHERE `id` = " + userId;
		} else if (username != null && alias != null) {
			queryUpdate += " WHERE `username` = '" + username
					+ "' AND `alias` = " + alias;
		} else {
			return 1;// update failed
		}
		initConnection();
		try {

			executeUpdate(queryUpdate);
		} catch (Exception e) {
			System.out.println(e);
			// TODO: handle exception
		}
		if (statement.getUpdateCount() > 0) {
			freeResources();
			return 0;
		} else {
			freeResources();
			return 1;
		}
	}

	@Override
	//新規ユーザを追加する
	public int insert() throws SQLException {
		String queryUpdate = "INSERT INTO " + TABLE_USER
				+ "(name,username,password,email,alias,access_level, status)"
				+ " VALUES('" + name + "','" + username + "','" + password
				+ "','" + email + "','" + alias + "'," + accessLevel + ", 0)";
		initConnection();
		try {
			executeUpdate(queryUpdate);
		} catch (SQLException e) {
			e.printStackTrace();
			freeResources();
			return 1;
		}
		if (statement.getUpdateCount() > 0) {
			freeResources();
			return 0;
		} else {
			freeResources();
			return 1;
		}
	}

	@Override
	//ユーザを削除する
	public int delete() throws SQLException {
		String queryUpdate = "DELETE FROM " + TABLE_USER;

		if (userId != 0) {
			queryUpdate += " WHERE `id` = " + userId;
		} else if (username != null && alias != null) {
			queryUpdate += " WHERE `username` = '" + username
					+ "' AND `alias` = '" + alias + "'";
		} else {
			return 1;// update failed
		}
		initConnection();
		executeUpdate(queryUpdate);
		if (statement.getUpdateCount() > 0) {
			freeResources();
			return 0;
		} else {
			freeResources();
			return 1;
		}
	}

	public String getAlias() {
		return alias;
	}

	public void setAlias(String alias) {
		this.alias = alias;
	}

	public int getStatus() {
		return this.status;
	}

	public void setStatus(int status) {
		this.status = status;
	}

	public int getUserId() {
		return this.userId;
	}

	public void setUserId(int userId) {
		this.userId = userId;
	}

	public String getUsername() {
		return this.username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getName() {
		return this.name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getEmail() {
		return this.email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public String getPassword() {
		return this.password;
	}

	public int getAccessLevel() {
		return accessLevel;
	}

	public void setAccessLevel(int accessLevel) {
		this.accessLevel = accessLevel;
	}

	public String getAliasRequest() {
		return aliasRequest;
	}

	public void setAliasRequest(String aliasRequest) {
		this.aliasRequest = aliasRequest;
	}

	/**
	 * 全てのユーザを収得する
	 * 
	 * @return
	 */
	// 団体の出題者・採点者の一覧を収得する
	public List<UserBean> listAllUser() throws SQLException {
		String query = "SELECT * from jp_user WHERE alias='" + alias
				+ "' AND (access_level=3 OR access_level=4)";

		initConnection();

		executeQuery(query);
		List<UserBean> listUser = new LinkedList<UserBean>();

		while (resultSet.next()) {
			UserBean usr = new UserBean();
			usr.userId = resultSet.getInt("id");
			usr.alias = resultSet.getString("alias");
			usr.name = resultSet.getString("name");
			usr.username = resultSet.getString("username");
			usr.accessLevel = resultSet.getInt("access_level");
			if (usr.accessLevel == 3)
				usr.userType = "出題者";
			if (usr.accessLevel == 4)
				usr.userType = "採点者";
			usr.email = resultSet.getString("email");
			listUser.add(usr);
		}
		freeResources();
		return listUser;
	}

	public String getUserType() {
		return userType;
	}

	public void setUserType(String userType) {
		this.userType = userType;
	}
//Idでユーザを削除する
	public int deleteUserById(int id) {
		try {
			initConnection();
			String query = "DELETE FROM jp_user WHERE id='" + id + "'";
			executeUpdate(query);
		} catch (Exception e) {
			System.out.println(e);
			return 1;
			// TODO: handle exception
		}
		return 0;

	}

}
